package com.logistics.analysis.data;

import java.sql.*;
import java.sql.Date;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ThreadLocalRandom;

/**
 * 物流数据生成器 - 简化版
 * 生成测试数据并插入到MySQL数据库中
 */
public class SimpleLogisticsDataGenerator {

    private Connection connection;
    private Random random = new Random();
    private static long waybillSequence = 1; // 运单序列号

    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://localhost:3306/logistics_analysis?useSSL=false&serverTimezone=UTC&characterEncoding=utf8&allowPublicKeyRetrieval=true";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "123456";

    // 预定义数据
    private static final String[] COMPANY_NAMES = {
        "顺丰速运", "圆通速递", "中通快递", "申通快递", "韵达速递",
        "百世快递", "德邦快递", "京东物流", "菜鸟网络", "邮政EMS"
    };

    // 根据地区ID获取地区位置信息
    private String getRegionLocation(int regionId) throws SQLException {
        String sql = "SELECT province, city FROM regions WHERE region_id = ?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(1, regionId);
        ResultSet rs = stmt.executeQuery();

        String location = "未知地区";
        if (rs.next()) {
            String province = rs.getString("province");
            String city = rs.getString("city");
            location = province + city;
        }

        rs.close();
        stmt.close();
        return location;
    }

    // 根据轨迹步骤生成合理的位置信息
    private String getTrackingLocation(int step, String senderLocation, String receiverLocation, int totalSteps) {
        if (step == 0) {
            // 第一步：在发货地
            return senderLocation;
        } else if (step == totalSteps - 1) {
            // 最后一步：在收货地
            return receiverLocation;
        } else {
            // 中间步骤：可能在中转站或运输途中
            if (step <= totalSteps / 2) {
                // 前半段：离开发货地或在中转站
                return senderLocation + "中转站";
            } else {
                // 后半段：到达收货地附近或中转站
                return receiverLocation + "中转站";
            }
        }
    }

    // 根据网点ID生成路线名称
    private String generateRouteNameByOutlets(int originOutletId, int destOutletId) throws SQLException {
        String originLocation = getOutletLocation(originOutletId);
        String destLocation = getOutletLocation(destOutletId);
        return originLocation + "-" + destLocation + "专线";
    }

    // 根据网点ID获取网点所在地区
    private String getOutletLocation(int outletId) throws SQLException {
        String sql = "SELECT r.province, r.city FROM outlets o JOIN regions r ON o.region_id = r.region_id WHERE o.outlet_id = ?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(1, outletId);
        ResultSet rs = stmt.executeQuery();
        
        String location = "未知地区";
        if (rs.next()) {
            String province = rs.getString("province");
            String city = rs.getString("city");
            location = province + city;
        }
        
        rs.close();
        stmt.close();
        return location;
    };

    private static final String[] COMPANY_CODES = {
        "SF", "YTO", "ZTO", "STO", "YUNDA",
        "BEST", "DEPPON", "JD", "CAINIAO", "EMS"
    };

    private static final String[] COMPANY_TYPES = {"国有", "民营", "外资", "合资"};

    private static final String[] PROVINCES = {
        "北京市", "上海市", "天津市", "重庆市", "河北省", "山西省", "辽宁省", "吉林省", "黑龙江省",
        "江苏省", "浙江省", "安徽省", "福建省", "江西省", "山东省", "河南省", "湖北省", "湖南省",
        "广东省", "海南省", "四川省", "贵州省", "云南省", "陕西省", "甘肃省", "青海省", "台湾省",
        "内蒙古自治区", "广西壮族自治区", "西藏自治区", "宁夏回族自治区", "新疆维吾尔自治区", "香港特别行政区", "澳门特别行政区"
    };

    private static final Map<String, String[]> CITIES = new HashMap<>();
    static {
        // 直辖市
        CITIES.put("北京市", new String[]{"东城区", "西城区", "朝阳区", "丰台区", "石景山区", "海淀区", "门头沟区", "房山区", "通州区", "顺义区", "昌平区", "大兴区", "怀柔区", "平谷区", "密云区", "延庆区"});
        CITIES.put("上海市", new String[]{"黄浦区", "徐汇区", "长宁区", "静安区", "普陀区", "虹口区", "杨浦区", "闵行区", "宝山区", "嘉定区", "浦东新区", "金山区", "松江区", "青浦区", "奉贤区", "崇明区"});
        CITIES.put("天津市", new String[]{"和平区", "河东区", "河西区", "南开区", "河北区", "红桥区", "东丽区", "西青区", "津南区", "北辰区", "武清区", "宝坻区", "滨海新区", "宁河区", "静海区", "蓟州区"});
        CITIES.put("重庆市", new String[]{"万州区", "涪陵区", "渝中区", "大渡口区", "江北区", "沙坪坝区", "九龙坡区", "南岸区", "北碚区", "綦江区", "大足区", "渝北区", "巴南区", "黔江区", "长寿区", "江津区", "合川区", "永川区", "南川区", "璧山区", "铜梁区", "潼南区", "荣昌区", "开州区", "梁平区", "武隆区"});

        // 省份
        CITIES.put("河北省", new String[]{"石家庄市", "唐山市", "秦皇岛市", "邯郸市", "邢台市", "保定市", "张家口市", "承德市", "沧州市", "廊坊市", "衡水市"});
        CITIES.put("山西省", new String[]{"太原市", "大同市", "阳泉市", "长治市", "晋城市", "朔州市", "晋中市", "运城市", "忻州市", "临汾市", "吕梁市"});
        CITIES.put("辽宁省", new String[]{"沈阳市", "大连市", "鞍山市", "抚顺市", "本溪市", "丹东市", "锦州市", "营口市", "阜新市", "辽阳市", "盘锦市", "铁岭市", "朝阳市", "葫芦岛市"});
        CITIES.put("吉林省", new String[]{"长春市", "吉林市", "四平市", "辽源市", "通化市", "白山市", "松原市", "白城市", "延边朝鲜族自治州"});
        CITIES.put("黑龙江省", new String[]{"哈尔滨市", "齐齐哈尔市", "鸡西市", "鹤岗市", "双鸭山市", "大庆市", "伊春市", "佳木斯市", "七台河市", "牡丹江市", "黑河市", "绥化市", "大兴安岭地区"});

        CITIES.put("江苏省", new String[]{"南京市", "无锡市", "徐州市", "常州市", "苏州市", "南通市", "连云港市", "淮安市", "盐城市", "扬州市", "镇江市", "泰州市", "宿迁市"});
        CITIES.put("浙江省", new String[]{"杭州市", "宁波市", "温州市", "嘉兴市", "湖州市", "绍兴市", "金华市", "衢州市", "舟山市", "台州市", "丽水市"});
        CITIES.put("安徽省", new String[]{"合肥市", "芜湖市", "蚌埠市", "淮南市", "马鞍山市", "淮北市", "铜陵市", "安庆市", "黄山市", "滁州市", "阜阳市", "宿州市", "六安市", "亳州市", "池州市", "宣城市"});
        CITIES.put("福建省", new String[]{"福州市", "厦门市", "莆田市", "三明市", "泉州市", "漳州市", "南平市", "龙岩市", "宁德市"});
        CITIES.put("江西省", new String[]{"南昌市", "景德镇市", "萍乡市", "九江市", "新余市", "鹰潭市", "赣州市", "吉安市", "宜春市", "抚州市", "上饶市"});
        CITIES.put("山东省", new String[]{"济南市", "青岛市", "淄博市", "枣庄市", "东营市", "烟台市", "潍坊市", "济宁市", "泰安市", "威海市", "日照市", "临沂市", "德州市", "聊城市", "滨州市", "菏泽市"});

        CITIES.put("河南省", new String[]{"郑州市", "开封市", "洛阳市", "平顶山市", "安阳市", "鹤壁市", "新乡市", "焦作市", "濮阳市", "许昌市", "漯河市", "三门峡市", "南阳市", "商丘市", "信阳市", "周口市", "驻马店市", "济源市"});
        CITIES.put("湖北省", new String[]{"武汉市", "黄石市", "十堰市", "宜昌市", "襄阳市", "鄂州市", "荆门市", "孝感市", "荆州市", "黄冈市", "咸宁市", "随州市", "恩施土家族苗族自治州", "仙桃市", "潜江市", "天门市", "神农架林区"});
        CITIES.put("湖南省", new String[]{"长沙市", "株洲市", "湘潭市", "衡阳市", "邵阳市", "岳阳市", "常德市", "张家界市", "益阳市", "郴州市", "永州市", "怀化市", "娄底市", "湘西土家族苗族自治州"});
        CITIES.put("广东省", new String[]{"广州市", "韶关市", "深圳市", "珠海市", "汕头市", "佛山市", "江门市", "湛江市", "茂名市", "肇庆市", "惠州市", "梅州市", "汕尾市", "河源市", "阳江市", "清远市", "东莞市", "中山市", "潮州市", "揭阳市", "云浮市"});
        CITIES.put("海南省", new String[]{"海口市", "三亚市", "三沙市", "儋州市", "五指山市", "琼海市", "文昌市", "万宁市", "东方市", "定安县", "屯昌县", "澄迈县", "临高县", "白沙黎族自治县", "昌江黎族自治县", "乐东黎族自治县", "陵水黎族自治县", "保亭黎族苗族自治县", "琼中黎族苗族自治县"});

        CITIES.put("四川省", new String[]{"成都市", "自贡市", "攀枝花市", "泸州市", "德阳市", "绵阳市", "广元市", "遂宁市", "内江市", "乐山市", "南充市", "眉山市", "宜宾市", "广安市", "达州市", "雅安市", "巴中市", "资阳市", "阿坝藏族羌族自治州", "甘孜藏族自治州", "凉山彝族自治州"});
        CITIES.put("贵州省", new String[]{"贵阳市", "六盘水市", "遵义市", "安顺市", "毕节市", "铜仁市", "黔西南布依族苗族自治州", "黔东南苗族侗族自治州", "黔南布依族苗族自治州"});
        CITIES.put("云南省", new String[]{"昆明市", "曲靖市", "玉溪市", "保山市", "昭通市", "丽江市", "普洱市", "临沧市", "楚雄彝族自治州", "红河哈尼族彝族自治州", "文山壮族苗族自治州", "西双版纳傣族自治州", "大理白族自治州", "德宏傣族景颇族自治州", "怒江傈僳族自治州", "迪庆藏族自治州"});

         CITIES.put("陕西省", new String[]{"西安市", "铜川市", "宝鸡市", "咸阳市", "渭南市", "延安市", "汉中市", "榆林市", "安康市", "商洛市"});
         CITIES.put("甘肃省", new String[]{"兰州市", "嘉峪关市", "金昌市", "白银市", "天水市", "武威市", "张掖市", "平凉市", "酒泉市", "庆阳市", "定西市", "陇南市", "临夏回族自治州", "甘南藏族自治州"});
         CITIES.put("青海省", new String[]{"西宁市", "海东市", "海北藏族自治州", "黄南藏族自治州", "海南藏族自治州", "果洛藏族自治州", "玉树藏族自治州", "海西蒙古族藏族自治州"});
         CITIES.put("台湾省", new String[]{"台北市", "新北市", "桃园市", "台中市", "台南市", "高雄市", "基隆市", "新竹市", "嘉义市", "新竹县", "苗栗县", "彰化县", "南投县", "云林县", "嘉义县", "屏东县", "宜兰县", "花莲县", "台东县", "澎湖县", "金门县", "连江县"});

         // 自治区
         CITIES.put("内蒙古自治区", new String[]{"呼和浩特市", "包头市", "乌海市", "赤峰市", "通辽市", "鄂尔多斯市", "呼伦贝尔市", "巴彦淖尔市", "乌兰察布市", "兴安盟", "锡林郭勒盟", "阿拉善盟"});
         CITIES.put("广西壮族自治区", new String[]{"南宁市", "柳州市", "桂林市", "梧州市", "北海市", "防城港市", "钦州市", "贵港市", "玉林市", "百色市", "贺州市", "河池市", "来宾市", "崇左市"});
         CITIES.put("西藏自治区", new String[]{"拉萨市", "日喀则市", "昌都市", "林芝市", "山南市", "那曲市", "阿里地区"});
         CITIES.put("宁夏回族自治区", new String[]{"银川市", "石嘴山市", "吴忠市", "固原市", "中卫市"});
         CITIES.put("新疆维吾尔自治区", new String[]{"乌鲁木齐市", "克拉玛依市", "吐鲁番市", "哈密市", "昌吉回族自治州", "博尔塔拉蒙古自治州", "巴音郭楞蒙古自治州", "阿克苏地区", "克孜勒苏柯尔克孜自治州", "喀什地区", "和田地区", "伊犁哈萨克自治州", "塔城地区", "阿勒泰地区", "石河子市", "阿拉尔市", "图木舒克市", "五家渠市", "北屯市", "铁门关市", "双河市", "可克达拉市", "昆玉市", "胡杨河市"});

         // 特别行政区
         CITIES.put("香港特别行政区", new String[]{"中西区", "湾仔区", "东区", "南区", "油尖旺区", "深水埗区", "九龙城区", "黄大仙区", "观塘区", "荃湾区", "屯门区", "元朗区", "北区", "大埔区", "沙田区", "西贡区", "离岛区", "葵青区"});
         CITIES.put("澳门特别行政区", new String[]{"澳门半岛", "氹仔", "路环"});
    };

    private static final String[] ECONOMIC_LEVELS = {"一线", "新一线", "二线", "三线", "四线", "五线"};
    private static final String[] OUTLET_TYPES = {"总部", "分拨中心", "转运中心", "营业网点", "代理点", "自提点"};
    private static final String[] AUTOMATION_LEVELS = {"全自动", "半自动", "人工"};
    private static final String[] CUSTOMER_TYPES = {"个人", "企业"};
    private static final String[] VEHICLE_TYPES = {"厢式货车", "平板货车", "冷藏车", "危险品车", "电动车", "摩托车"};
    private static final String[] FUEL_TYPES = {"汽油", "柴油", "电动", "混合动力"};
    private static final String[] GOODS_TYPES = {"文件", "数码产品", "服装", "食品", "日用品", "工业品", "其他"};
    private static final String[] SERVICE_TYPES = {"标准快递", "特快专递", "经济快递", "同城配送", "次日达", "当日达"};
    private static final String[] PAYMENT_METHODS = {"寄付", "到付", "第三方付"};
    private static final String[] WAYBILL_STATUSES = {"已下单", "已揽收", "运输中", "派送中", "已签收", "异常", "退回"};
    private static final String[] WORK_TYPES = {"揽收", "派送", "揽派一体"};
    private static final String[] EDUCATION_LEVELS = {"小学", "初中", "高中", "大专", "本科", "研究生"};
    private static final String[] COURIER_STATUSES = {"在职", "离职", "休假", "培训"};

    private static final String[] FIRST_NAMES = {"王", "李", "张", "刘", "陈", "杨", "赵", "黄", "周", "吴", "徐", "孙", "马", "朱", "胡", "郭", "何", "高", "林", "罗", "郑", "梁", "谢", "宋", "唐", "许", "韩", "冯", "邓", "曹"};
    private static final String[] LAST_NAMES = {"伟", "芳", "娜", "敏", "静", "丽", "强", "磊", "军", "洋", "子", "杰", "浩", "欣", "雨", "轩", "梓", "思", "怡", "博", "宇", "嘉", "明", "晨", "佳", "子豪", "雨桐", "思源", "思远", "志强"};

    public SimpleLogisticsDataGenerator() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            this.connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            this.connection.setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        SimpleLogisticsDataGenerator generator = new SimpleLogisticsDataGenerator();
        try {
            System.out.println("开始生成物流数据...");

            // 解析命令行参数，控制是否清空数据
            boolean clearData = true;
            for (String arg : args) {
                if ("--no-clear".equals(arg)) {
                    clearData = false;
                    break;
                }
            }

            // 0. 根据参数决定是否清空现有数据
            if (clearData) {
                generator.clearAllTables();
                generator.connection.commit(); // 提交清空操作
                System.out.println("✓ 清空现有数据完成");
            } else {
                System.out.println("✓ 保留现有数据，进行增量生成");
            }

            // 1. 生成快递公司数据
            generator.generateExpressCompanies();
            generator.connection.commit(); // 提交快递公司数据
            System.out.println("✓ 快递公司数据生成完成");

            // 2. 生成地区数据
            generator.generateRegions();
            generator.connection.commit(); // 提交地区数据
            System.out.println("✓ 地区数据生成完成");

            // 3. 生成网点数据
            generator.generateOutlets();
            generator.connection.commit(); // 提交网点数据
            System.out.println("✓ 网点数据生成完成");

            // 4. 生成客户数据
            generator.generateCustomers();
            generator.connection.commit(); // 提交客户数据
            System.out.println("✓ 客户数据生成完成");

            // 5. 生成车辆数据
            generator.generateVehicles();
            generator.connection.commit(); // 提交车辆数据
            System.out.println("✓ 车辆数据生成完成");

            // 6. 生成司机数据
            generator.generateDrivers();
            generator.connection.commit(); // 提交司机数据
            System.out.println("✓ 司机数据生成完成");

            // 7. 生成快递员数据
            generator.generateCouriers();
            generator.connection.commit(); // 提交快递员数据
            System.out.println("✓ 快递员数据生成完成");

            // 8. 生成运单数据
            generator.generateWaybills();
            generator.connection.commit(); // 提交运单数据
            System.out.println("✓ 运单数据生成完成");

            // 9. 生成运单轨迹数据
            generator.generateWaybillTracking();
            generator.connection.commit(); // 提交运单轨迹数据
            System.out.println("✓ 运单轨迹数据生成完成");

            // 10. 生成价格配置数据
            generator.generatePricingConfig();
            generator.connection.commit(); // 提交价格配置数据
            System.out.println("✓ 价格配置数据生成完成");

            // 11. 生成异常信息数据
            generator.generateExceptions();
            generator.connection.commit(); // 提交异常信息数据
            System.out.println("✓ 异常信息数据生成完成");

            // 12. 生成客户满意度数据
            generator.generateCustomerSatisfaction();
            generator.connection.commit(); // 提交客户满意度数据
            System.out.println("✓ 客户满意度数据生成完成");

            // 13. 生成运输路线数据
            generator.generateTransportRoutes();
            generator.connection.commit(); // 提交运输路线数据
            System.out.println("✓ 运输路线数据生成完成");

            // 14. 生成客户行为分析数据
            generator.generateCustomerBehavior();
            generator.connection.commit(); // 提交客户行为分析数据
            System.out.println("✓ 客户行为分析数据生成完成");

            // 15. 生成时效分析数据
            generator.generateDeliveryTimeAnalysis();
            generator.connection.commit(); // 提交时效分析数据
            System.out.println("✓ 时效分析数据生成完成");

            System.out.println("所有数据生成完成！");

        } catch (Exception e) {
            e.printStackTrace();
            try {
                generator.connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                generator.connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 清空所有表数据
    private void clearAllTables() throws SQLException {
        // 按照外键依赖关系的逆序删除数据
        String[] tables = {
            "delivery_time_analysis",
            "customer_behavior",
            "transport_routes",
            "customer_satisfaction",
            "exceptions",
            "pricing_config",
            "waybill_tracking",
            "waybills",
            "couriers",
            "drivers",
            "vehicles",
            "customers",
            "outlets",
            "regions",
            "express_companies"
        };

        // 临时禁用外键检查
        if (connection == null) {
            throw new SQLException("数据库连接未初始化");
        }
        Statement stmt = connection.createStatement();
        stmt.execute("SET FOREIGN_KEY_CHECKS = 0");

        // 注释掉清空表逻辑，保留现有数据
        /*// 清空现有数据
        for (String table : tables) {
            try {
                stmt.execute("DELETE FROM " + table);
                // 重置AUTO_INCREMENT
                stmt.execute("ALTER TABLE " + table + " AUTO_INCREMENT = 1");
                System.out.println("清空表: " + table);
            } catch (SQLException e) {
                System.out.println("清空表 " + table + " 时出错: " + e.getMessage());
            }
        }*/

        // 重新启用外键检查
        stmt.execute("SET FOREIGN_KEY_CHECKS = 1");
        stmt.close();
    }

    // 1. 生成快递公司数据
    private void generateExpressCompanies() throws SQLException {
        String sql = "INSERT INTO express_companies (company_name, company_code, company_type, established_date, headquarters, contact_phone, website, business_scope) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        System.out.println("准备生成 " + COMPANY_NAMES.length + " 家快递公司数据...");
        for (int i = 0; i < COMPANY_NAMES.length; i++) {
            // Check if company_code already exists
            String checkSql = "SELECT COUNT(*) FROM express_companies WHERE company_code = ?";
            PreparedStatement checkStmt = connection.prepareStatement(checkSql);
            checkStmt.setString(1, COMPANY_CODES[i]);
            ResultSet rs = checkStmt.executeQuery();
            rs.next();
            int count = rs.getInt(1);
            rs.close();
            checkStmt.close();

            if (count == 0) {
                stmt.setString(1, COMPANY_NAMES[i]);
                stmt.setString(2, COMPANY_CODES[i]);
                stmt.setString(3, COMPANY_TYPES[random.nextInt(COMPANY_TYPES.length)]);
                stmt.setDate(4, Date.valueOf(LocalDate.of(1990 + random.nextInt(30), 1 + random.nextInt(12), 1 + random.nextInt(28))));
                stmt.setString(5, PROVINCES[random.nextInt(PROVINCES.length)]);
                stmt.setString(6, generatePhoneNumber());
                stmt.setString(7, "http://www." + COMPANY_CODES[i].toLowerCase() + ".com");
                stmt.setString(8, "国内快递、国际快递、仓储配送");
                stmt.addBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();

        // 验证数据是否正确插入，并显示实际的公司ID
        Statement verifyStmt = connection.createStatement();
        ResultSet rs = verifyStmt.executeQuery("SELECT company_id, company_name FROM express_companies ORDER BY company_id");
        System.out.println("实际插入的快递公司信息:");
        while (rs.next()) {
            System.out.println("ID: " + rs.getInt("company_id") + ", 名称: " + rs.getString("company_name"));
        }
        rs.close();

        // 检查公司数量
        rs = verifyStmt.executeQuery("SELECT COUNT(*) as count FROM express_companies");
        if (rs.next()) {
            System.out.println("总共插入的快递公司数量: " + rs.getInt("count"));
        }
        rs.close();
        verifyStmt.close();
    }

    // 计算总地区数量
    private static int getTotalRegionCount() {
        int count = PROVINCES.length; // 省级数量
        for (String province : PROVINCES) {
            String[] cities = CITIES.get(province);
            if (cities != null) {
                count += cities.length; // 市级数量
            }
        }
        return count;
    }

    /**
     * 获取指定表中指定前缀编码的最大数字部分
     */
    private int getMaxCodeNumber(String tableName, String columnName, String prefix) throws SQLException {
        String sql = "SELECT MAX(" + columnName + ") FROM " + tableName;
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                String maxCode = rs.getString(1);
                if (maxCode != null && maxCode.startsWith(prefix)) {
                    String numberPart = maxCode.substring(prefix.length());
                    return Integer.parseInt(numberPart);
                }
            }
        } catch (SQLException e) {
            System.out.println("获取最大编码失败: " + e.getMessage());
        } catch (NumberFormatException e) {
            System.out.println("编码格式解析错误: " + e.getMessage());
        }
        return 0;
    }

    // 2. 生成地区数据
    private void generateRegions() throws SQLException {
        String sql = "INSERT INTO regions (region_name, region_code, parent_id, region_level, province, city, district, postal_code, area_code, population, area_size, economic_level, gdp, longitude, latitude) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        int regionId = 1;

        // 生成省级数据
        for (String province : PROVINCES) {
            stmt.setString(1, province);
            stmt.setString(2, String.format("%04d", regionId));
            stmt.setNull(3, Types.INTEGER);
            stmt.setInt(4, 1); // 省级
            stmt.setString(5, province);
            stmt.setNull(6, Types.VARCHAR);
            stmt.setNull(7, Types.VARCHAR);
            stmt.setString(8, String.format("%06d", 100000 + random.nextInt(900000)));
            stmt.setString(9, String.format("%03d", random.nextInt(1000)));
            stmt.setLong(10, 1000000 + random.nextInt(50000000));
            stmt.setBigDecimal(11, new java.math.BigDecimal(10000 + random.nextInt(90000)));
            stmt.setString(12, ECONOMIC_LEVELS[random.nextInt(ECONOMIC_LEVELS.length)]);
            stmt.setBigDecimal(13, new java.math.BigDecimal(50000 + random.nextInt(200000)));
            stmt.setBigDecimal(14, new java.math.BigDecimal(73 + random.nextDouble() * 62)); // 经度
            stmt.setBigDecimal(15, new java.math.BigDecimal(18 + random.nextDouble() * 35)); // 纬度
            stmt.addBatch();
            regionId++;
        }

        // 生成市级数据（为所有省份生成）
        for (String province : PROVINCES) {
            String[] cities = CITIES.get(province);
            if (cities != null) {
                for (String city : cities) {
                    stmt.setString(1, city);
                    stmt.setString(2, String.format("%04d", regionId));
                    stmt.setInt(3, Arrays.asList(PROVINCES).indexOf(province) + 1); // 父级ID
                    stmt.setInt(4, 2); // 市级
                    stmt.setString(5, province);
                    stmt.setString(6, city);
                    stmt.setNull(7, Types.VARCHAR);
                    stmt.setString(8, String.format("%06d", 100000 + random.nextInt(900000)));
                    stmt.setString(9, String.format("%03d", random.nextInt(1000)));
                    stmt.setLong(10, 100000 + random.nextInt(5000000));
                    stmt.setBigDecimal(11, new java.math.BigDecimal(1000 + random.nextInt(9000)));
                    stmt.setString(12, ECONOMIC_LEVELS[random.nextInt(ECONOMIC_LEVELS.length)]);
                    stmt.setBigDecimal(13, new java.math.BigDecimal(5000 + random.nextInt(50000)));
                    stmt.setBigDecimal(14, new java.math.BigDecimal(73 + random.nextDouble() * 62));
                    stmt.setBigDecimal(15, new java.math.BigDecimal(18 + random.nextDouble() * 35));
                    stmt.addBatch();
                    regionId++;
                }
            }
        }

        stmt.executeBatch();
        stmt.close();

        System.out.println("总共生成地区数量: " + (regionId - 1) + " (省级: " + PROVINCES.length + ", 市级: " + (regionId - 1 - PROVINCES.length) + ")");
    }

    // 3. 生成网点数据
    private void generateOutlets() throws SQLException {
        String sql = "INSERT INTO outlets (outlet_code, outlet_name, company_id, region_id, outlet_type, address, contact_person, contact_phone, business_hours, daily_capacity, automation_level, operating_cost, staff_count, warehouse_area, longitude, latitude) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        int totalRegions = getTotalRegionCount();
        System.out.println("准备生成网点数据，公司ID范围: 1 到 " + COMPANY_NAMES.length + ", 地区ID范围: 1 到 " + totalRegions);

        // 获取现有最大网点编码数字部分
        int startNum = getMaxCodeNumber("outlets", "outlet_code", "WD") + 1;
        for (int i = startNum; i < startNum + 1500; i++) {
            int companyId = 1 + random.nextInt(COMPANY_NAMES.length);
            int regionId = 1 + random.nextInt(totalRegions);

            if (i <= 5) { // 只打印前5个的调试信息
                System.out.println("网点 " + i + " 使用公司ID: " + companyId + ", 地区ID: " + regionId);
            }

            stmt.setString(1, String.format("WD%06d", i));
            stmt.setString(2, generateOutletNameByRegion(regionId)); // 使用关联的网点名称
            stmt.setInt(3, companyId);
            stmt.setInt(4, regionId);
            stmt.setString(5, OUTLET_TYPES[random.nextInt(OUTLET_TYPES.length)]);
            stmt.setString(6, generateAddressByRegion(regionId)); // 使用关联的地址
            stmt.setString(7, generatePersonName());
            stmt.setString(8, generatePhoneNumber());
            stmt.setString(9, "08:00-18:00");
            stmt.setInt(10, 100 + random.nextInt(1000));
            stmt.setString(11, AUTOMATION_LEVELS[random.nextInt(AUTOMATION_LEVELS.length)]);
            stmt.setBigDecimal(12, new java.math.BigDecimal(5000 + random.nextInt(20000)));
            stmt.setInt(13, 5 + random.nextInt(50));
            stmt.setBigDecimal(14, new java.math.BigDecimal(100 + random.nextInt(1000)));
            stmt.setBigDecimal(15, new java.math.BigDecimal(73 + random.nextDouble() * 62));
            stmt.setBigDecimal(16, new java.math.BigDecimal(18 + random.nextDouble() * 35));
            stmt.addBatch();

            if (i % 100 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 4. 生成客户数据
    private void generateCustomers() throws SQLException {
        String sql = "INSERT INTO customers (customer_code, customer_name, customer_type, phone, email, id_card, region_id, address, credit_level, total_orders, total_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        int totalRegions = getTotalRegionCount();
        // 获取现有最大客户编码数字部分
        int startNum = getMaxCodeNumber("customers", "customer_code", "CU") + 1;
        for (int i = startNum; i < startNum + 3000; i++) {
            String customerType = CUSTOMER_TYPES[random.nextInt(CUSTOMER_TYPES.length)];
            int regionId = 1 + random.nextInt(totalRegions);

            stmt.setString(1, String.format("CU%08d", i));
            stmt.setString(2, customerType.equals("个人") ? generatePersonName() : generateCompanyName());
            stmt.setString(3, customerType);
            stmt.setString(4, generatePhoneNumber());
            stmt.setString(5, generateEmail());
            stmt.setString(6, customerType.equals("个人") ? generateIdCard() : null);
            stmt.setInt(7, regionId);
            stmt.setString(8, generateAddressByRegion(regionId)); // 使用关联的地址
            stmt.setString(9, new String[]{"A", "B", "C", "D"}[random.nextInt(4)]);
            stmt.setInt(10, random.nextInt(100));
            stmt.setBigDecimal(11, new java.math.BigDecimal(random.nextInt(50000)));
            stmt.addBatch();

            if (i % 100 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 5. 生成车辆数据
    private void generateVehicles() throws SQLException {
        String sql = "INSERT INTO vehicles (vehicle_code, license_plate, company_id, vehicle_type, vehicle_model, load_capacity, volume_capacity, fuel_type, purchase_date, purchase_price, mileage, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        String[] vehicleModels = {
            "解放J6P", "东风天龙", "重汽豪沃", "陕汽德龙", "福田欧曼",
            "江淮格尔发", "北奔重卡", "上汽红岩", "华菱星马", "大运重卡"
        };

        String[] vehicleStatuses = {"正常", "维修", "报废"};
        // 车辆状态分布：正常80%，维修18%，报废2%
        double[] statusProbabilities = {0.80, 0.18, 0.02};

        for (int i = 1; i <= 600; i++) {
            String vehicleType = VEHICLE_TYPES[random.nextInt(VEHICLE_TYPES.length)];
            String vehicleModel = vehicleModels[random.nextInt(vehicleModels.length)];

            // 根据车辆类型调整载重和容积
            double loadCapacity, volumeCapacity;
            switch (vehicleType) {
                case "厢式货车":
                    loadCapacity = 3.0 + random.nextDouble() * 7.0; // 3-10吨
                    volumeCapacity = 15.0 + random.nextDouble() * 25.0; // 15-40立方米
                    break;
                case "平板货车":
                    loadCapacity = 5.0 + random.nextDouble() * 10.0; // 5-15吨
                    volumeCapacity = 20.0 + random.nextDouble() * 30.0; // 20-50立方米
                    break;
                case "冷藏车":
                    loadCapacity = 2.0 + random.nextDouble() * 6.0; // 2-8吨
                    volumeCapacity = 10.0 + random.nextDouble() * 20.0; // 10-30立方米
                    break;
                case "危险品车":
                    loadCapacity = 1.0 + random.nextDouble() * 4.0; // 1-5吨
                    volumeCapacity = 5.0 + random.nextDouble() * 15.0; // 5-20立方米
                    break;
                case "电动车":
                    loadCapacity = 0.5 + random.nextDouble() * 1.5; // 0.5-2吨
                    volumeCapacity = 2.0 + random.nextDouble() * 8.0; // 2-10立方米
                    break;
                case "摩托车":
                    loadCapacity = 0.1 + random.nextDouble() * 0.4; // 0.1-0.5吨
                    volumeCapacity = 0.5 + random.nextDouble() * 1.5; // 0.5-2立方米
                    break;
                default:
                    loadCapacity = 2.0 + random.nextDouble() * 8.0;
                    volumeCapacity = 10.0 + random.nextDouble() * 40.0;
            }

            // 根据概率分配车辆状态
            String vehicleStatus = "正常";
            double rand = random.nextDouble();
            double cumulative = 0;
            for (int j = 0; j < statusProbabilities.length; j++) {
                cumulative += statusProbabilities[j];
                if (rand < cumulative) {
                    vehicleStatus = vehicleStatuses[j];
                    break;
                }
            }

            // 根据车辆状态调整里程数
            int mileage;
            switch (vehicleStatus) {
                case "报废":
                    mileage = 800000 + random.nextInt(500000); // 报废车辆里程较高
                    break;
                case "维修":
                    mileage = 200000 + random.nextInt(600000); // 维修车辆里程分布较广
                    break;
                default:
                    mileage = random.nextInt(500000); // 正常车辆里程正常分布
            }

            stmt.setString(1, String.format("VH%06d", i));
            stmt.setString(2, generateLicensePlate());
            stmt.setInt(3, 1 + random.nextInt(COMPANY_NAMES.length));
            stmt.setString(4, vehicleType);
            stmt.setString(5, vehicleModel);
            stmt.setBigDecimal(6, new java.math.BigDecimal(loadCapacity).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(7, new java.math.BigDecimal(volumeCapacity).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setString(8, FUEL_TYPES[random.nextInt(FUEL_TYPES.length)]);
            stmt.setDate(9, Date.valueOf(LocalDate.of(2015 + random.nextInt(8), 1 + random.nextInt(12), 1 + random.nextInt(28))));
            stmt.setBigDecimal(10, new java.math.BigDecimal(100000 + random.nextInt(400000)));
            stmt.setInt(11, mileage);
            stmt.setString(12, vehicleStatus);
            stmt.addBatch();

            if (i % 50 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 6. 生成司机数据
    private void generateDrivers() throws SQLException {
        String sql = "INSERT INTO drivers (driver_code, driver_name, company_id, id_card, phone, license_number, license_type, hire_date, birth_date, gender, driving_experience, safety_score, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        String[] statuses = {"在职", "离职", "休假"};
        // 设置状态分布：70%在职，20%离职，10%休假
        double[] statusProbabilities = {0.7, 0.2, 0.1};

        for (int i = 1; i <= 2500; i++) {
            String gender = random.nextBoolean() ? "男" : "女";

            // 根据概率分配状态
            String status = "在职";
            double rand = random.nextDouble();
            if (rand < statusProbabilities[0]) {
                status = "在职";
            } else if (rand < statusProbabilities[0] + statusProbabilities[1]) {
                status = "离职";
            } else {
                status = "休假";
            }

            stmt.setString(1, String.format("DR%06d", i));
            stmt.setString(2, generatePersonName());
            stmt.setInt(3, 1 + random.nextInt(COMPANY_NAMES.length));
            stmt.setString(4, generateIdCard());
            stmt.setString(5, generatePhoneNumber());
            stmt.setString(6, generateLicenseNumber());
            stmt.setString(7, new String[]{"A1", "A2", "B1", "B2", "C1"}[random.nextInt(5)]);
            stmt.setDate(8, Date.valueOf(LocalDate.of(2010 + random.nextInt(13), 1 + random.nextInt(12), 1 + random.nextInt(28))));
            stmt.setDate(9, Date.valueOf(LocalDate.of(1970 + random.nextInt(30), 1 + random.nextInt(12), 1 + random.nextInt(28))));
            stmt.setString(10, gender);
            stmt.setInt(11, 1 + random.nextInt(20));

            // 根据状态调整安全评分
            int baseScore = 80;
            if ("离职".equals(status)) {
                // 离职司机可能安全评分较低
                baseScore = 60 + random.nextInt(30);
            } else if ("休假".equals(status)) {
                // 休假司机通常表现良好
                baseScore = 85 + random.nextInt(15);
            } else {
                // 在职司机正常分布
                baseScore = 80 + random.nextInt(20);
            }

            stmt.setBigDecimal(12, new java.math.BigDecimal(baseScore));
            stmt.setString(13, status);
            stmt.addBatch();

            if (i % 50 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 7. 生成快递员数据
    private void generateCouriers() throws SQLException {
        String sql = "INSERT INTO couriers (courier_code, courier_name, company_id, outlet_id, id_card, phone, hire_date, birth_date, gender, service_area, work_type, daily_capacity, service_score, delivery_success_rate, customer_complaint_count, monthly_salary, commission_rate, work_experience, education_level, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        // 设置状态分布：80%在职，10%离职，5%休假，5%培训
        double[] statusProbabilities = {0.8, 0.1, 0.05, 0.05};

        // 工作类型分布：60%揽派一体，25%派送，15%揽收
        double[] workTypeProbabilities = {0.15, 0.25, 0.6};

        int totalOutlets = getTotalOutletCount();

        for (int i = 1; i <= 1200; i++) {
            String gender = random.nextBoolean() ? "男" : "女";

            // 根据概率分配状态
            String status = "在职";
            double rand = random.nextDouble();
            if (rand < statusProbabilities[0]) {
                status = "在职";
            } else if (rand < statusProbabilities[0] + statusProbabilities[1]) {
                status = "离职";
            } else if (rand < statusProbabilities[0] + statusProbabilities[1] + statusProbabilities[2]) {
                status = "休假";
            } else {
                status = "培训";
            }

            // 根据概率分配工作类型
            String workType = "揽派一体";
            rand = random.nextDouble();
            if (rand < workTypeProbabilities[0]) {
                workType = "揽收";
            } else if (rand < workTypeProbabilities[0] + workTypeProbabilities[1]) {
                workType = "派送";
            } else {
                workType = "揽派一体";
            }

            // 根据工作类型设置日处理能力
            int dailyCapacity;
            switch (workType) {
                case "揽收":
                    dailyCapacity = 80 + random.nextInt(40); // 80-120件
                    break;
                case "派送":
                    dailyCapacity = 100 + random.nextInt(50); // 100-150件
                    break;
                case "揽派一体":
                    dailyCapacity = 60 + random.nextInt(40); // 60-100件
                    break;
                default:
                    dailyCapacity = 80 + random.nextInt(40);
            }

            // 根据状态调整服务评分和派送成功率
            double serviceScore = 4.0 + random.nextDouble(); // 4.0-5.0分
            double deliverySuccessRate = 90.0 + random.nextDouble() * 10; // 90%-100%
            int complaintCount = 0;

            if ("离职".equals(status)) {
                // 离职快递员可能服务质量较差
                serviceScore = 3.0 + random.nextDouble() * 1.5; // 3.0-4.5分
                deliverySuccessRate = 80.0 + random.nextDouble() * 15; // 80%-95%
                complaintCount = random.nextInt(10); // 0-9次投诉
            } else if ("培训".equals(status)) {
                // 培训中的快递员评分可能较低
                serviceScore = 3.5 + random.nextDouble() * 1.0; // 3.5-4.5分
                deliverySuccessRate = 85.0 + random.nextDouble() * 10; // 85%-95%
                complaintCount = random.nextInt(5); // 0-4次投诉
            } else {
                // 在职和休假快递员正常分布
                complaintCount = random.nextInt(3); // 0-2次投诉
            }

            // 生成工资和提成
            double monthlySalary = 3000 + random.nextInt(3000); // 3000-6000元基本工资
            double commissionRate = 0.5 + random.nextDouble() * 2.0; // 0.5%-2.5%提成

            // 生成工作经验和学历
            int workExperience = random.nextInt(15); // 0-14年经验
            // 调整学历分布，更符合现实情况
            double eduRand = random.nextDouble();
            String educationLevel;
            if (eduRand < 0.05) { // 5% 小学
                educationLevel = EDUCATION_LEVELS[0];
            } else if (eduRand < 0.20) { // 15% 初中
                educationLevel = EDUCATION_LEVELS[1];
            } else if (eduRand < 0.50) { // 30% 高中
                educationLevel = EDUCATION_LEVELS[2];
            } else if (eduRand < 0.80) { // 30% 大专
                educationLevel = EDUCATION_LEVELS[3];
            } else if (eduRand < 0.95) { // 15% 本科
                educationLevel = EDUCATION_LEVELS[4];
            } else { // 5% 研究生
                educationLevel = EDUCATION_LEVELS[5];
            }

            // 生成服务区域
            String serviceArea = generateServiceArea();

            stmt.setString(1, String.format("CR%06d", i));
            stmt.setString(2, generatePersonName());
            stmt.setInt(3, 1 + random.nextInt(COMPANY_NAMES.length));
            stmt.setInt(4, 1 + random.nextInt(totalOutlets));
            stmt.setString(5, generateIdCard());
            stmt.setString(6, generatePhoneNumber());
            stmt.setDate(7, Date.valueOf(LocalDate.of(2015 + random.nextInt(8), 1 + random.nextInt(12), 1 + random.nextInt(28))));
            // 调整年龄分布：18-53岁，重点分布在25-40岁
            int birthYear; 
            double ageRand = random.nextDouble();
            if (ageRand < 0.1) { // 10%: 1970-1979年出生(44-53岁)
                birthYear = 1970 + random.nextInt(10);
            } else if (ageRand < 0.3) { // 20%: 1980-1989年出生(34-43岁)
                birthYear = 1980 + random.nextInt(10);
            } else if (ageRand < 0.6) { // 30%: 1990-1999年出生(24-33岁)
                birthYear = 1990 + random.nextInt(10);
            } else if (ageRand < 0.85) { // 25%: 2000-2004年出生(19-23岁)
                birthYear = 2000 + random.nextInt(5);
            } else { // 15%: 2005-2005年出生(18岁)
                birthYear = 2005;
            }
            stmt.setDate(8, Date.valueOf(LocalDate.of(birthYear, 1 + random.nextInt(12), 1 + random.nextInt(28))));
            stmt.setString(9, gender);
            stmt.setString(10, serviceArea);
            stmt.setString(11, workType);
            stmt.setInt(12, dailyCapacity);
            stmt.setBigDecimal(13, new java.math.BigDecimal(String.format("%.2f", serviceScore)));
            stmt.setBigDecimal(14, new java.math.BigDecimal(String.format("%.2f", deliverySuccessRate)));
            stmt.setInt(15, complaintCount);
            stmt.setBigDecimal(16, new java.math.BigDecimal(monthlySalary));
            stmt.setBigDecimal(17, new java.math.BigDecimal(String.format("%.2f", commissionRate)));
            stmt.setInt(18, workExperience);
            stmt.setString(19, educationLevel);
            stmt.setString(20, status);
            stmt.addBatch();

            if (i % 50 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 8. 生成运单数据
    private void generateWaybills() throws SQLException {
        String sql = "INSERT INTO waybills (waybill_number, company_id, sender_customer_id, receiver_name, receiver_phone, sender_region_id, receiver_region_id, sender_address, receiver_address, origin_outlet_id, dest_outlet_id, goods_name, goods_type, goods_weight, goods_volume, goods_value, service_type, payment_method, base_fee, weight_fee, distance_fee, service_fee, insurance_fee, total_amount, waybill_status, create_time, pickup_time, delivery_time, sign_time, actual_delivery_time, pickup_courier_id, delivery_courier_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        // 运单状态分布：已签收60%，运输中15%，派送中10%，已揽收8%，异常4%，已下单2%，退回1%
        String[] statuses = {"已签收", "运输中", "派送中", "已揽收", "异常", "已下单", "退回"};
        double[] statusProbabilities = {0.6, 0.15, 0.1, 0.08, 0.04, 0.02, 0.01};

        int totalRegions = getTotalRegionCount();
        for (int i = 1; i <= 8000; i++) {
            LocalDateTime createTime = LocalDateTime.now().minusDays(random.nextInt(30));

            // 根据概率分配运单状态
            String waybillStatus = "已下单";
            double rand = random.nextDouble();
            double cumulative = 0;
            for (int j = 0; j < statusProbabilities.length; j++) {
                cumulative += statusProbabilities[j];
                if (rand < cumulative) {
                    waybillStatus = statuses[j];
                    break;
                }
            }

            // 生成货物信息
            double goodsWeight = 0.1 + random.nextDouble() * 10; // 0.1-10.1公斤
            double goodsVolume = 0.01 + random.nextDouble(); // 0.01-1.01立方米
            double goodsValue = 10 + random.nextInt(1000); // 10-1010元
            String serviceType = SERVICE_TYPES[random.nextInt(SERVICE_TYPES.length)];

            // 计算距离（模拟）
            int distance = 50 + random.nextInt(2000); // 50-2050公里

            // 根据服务类型计算基础费用
            double baseFee;
            switch (serviceType) {
                case "当日达":
                    baseFee = 15 + random.nextInt(10); // 15-25元
                    break;
                case "次日达":
                    baseFee = 12 + random.nextInt(8); // 12-20元
                    break;
                case "标准快递":
                    baseFee = 8 + random.nextInt(7); // 8-15元
                    break;
                case "经济快递":
                    baseFee = 5 + random.nextInt(5); // 5-10元
                    break;
                case "特快专递":
                    baseFee = 20 + random.nextInt(15); // 20-35元
                    break;
                case "同城配送":
                    baseFee = 6 + random.nextInt(4); // 6-10元
                    break;
                default:
                    baseFee = 8 + random.nextInt(12);
            }

            // 计算重量费（超过1公斤的部分）
            double weightFee = 0;
            if (goodsWeight > 1.0) {
                double extraWeight = goodsWeight - 1.0;
                double weightRate = serviceType.equals("当日达") ? 5.0 :
                                   serviceType.equals("次日达") ? 4.0 :
                                   serviceType.equals("特快专递") ? 6.0 : 3.0;
                weightFee = Math.ceil(extraWeight) * weightRate;
            }

            // 计算距离费（超过100公里的部分）
            double distanceFee = 0;
            if (distance > 100) {
                double extraDistance = distance - 100;
                double distanceRate = serviceType.equals("当日达") ? 0.8 :
                                     serviceType.equals("次日达") ? 0.6 :
                                     serviceType.equals("特快专递") ? 1.0 : 0.4;
                distanceFee = (extraDistance / 100) * distanceRate;
            }

            // 计算服务费
            double serviceFee = 0;
            if (serviceType.equals("当日达") || serviceType.equals("特快专递")) {
                serviceFee = baseFee * 0.2; // 加急服务费为基础费用的20%
            }

            // 计算保险费（如果货物价值较高）
            double insuranceFee = 0;
            boolean isInsured = goodsValue > 100 && random.nextDouble() < 0.3; // 30%概率保价
            if (isInsured) {
                insuranceFee = goodsValue * 0.005; // 保险费为货物价值的0.5%
            }

            // 计算总费用
            double totalAmount = baseFee + weightFee + distanceFee + serviceFee + insuranceFee;

            // 根据状态设置相应的时间戳
            LocalDateTime pickupTime = null;
            LocalDateTime deliveryTime = null;
            LocalDateTime signTime = null;
            LocalDateTime actualDeliveryTime = null;

            switch (waybillStatus) {
                case "已下单":
                    // 只有下单时间
                    break;
                case "已揽收":
                    pickupTime = createTime.plusHours(1 + random.nextInt(24));
                    break;
                case "运输中":
                    pickupTime = createTime.plusHours(1 + random.nextInt(24));
                    break;
                case "派送中":
                    pickupTime = createTime.plusHours(1 + random.nextInt(24));
                    deliveryTime = pickupTime.plusHours(12 + random.nextInt(48));
                    break;
                case "已签收":
                    pickupTime = createTime.plusHours(1 + random.nextInt(24));
                    deliveryTime = pickupTime.plusHours(12 + random.nextInt(48));
                    signTime = deliveryTime.plusHours(1 + random.nextInt(12));
                    actualDeliveryTime = signTime;
                    break;
                case "异常":
                    pickupTime = createTime.plusHours(1 + random.nextInt(24));
                    // 异常状态可能在任何阶段发生
                    if (random.nextBoolean()) {
                        deliveryTime = pickupTime.plusHours(12 + random.nextInt(48));
                    }
                    break;
                case "退回":
                    pickupTime = createTime.plusHours(1 + random.nextInt(24));
                    deliveryTime = pickupTime.plusHours(12 + random.nextInt(48));
                    // 退回通常在派送阶段发生
                    break;
            }

            // 根据运单状态分配快递员
            Integer pickupCourierId = null;
            Integer deliveryCourierId = null;
            int totalCouriers = getTotalCourierCount();

            // 如果运单已经被揽收或更进一步的状态，分配揽收快递员
            if (!waybillStatus.equals("已下单")) {
                pickupCourierId = 1 + random.nextInt(totalCouriers); // 快递员ID范围1-totalCouriers
            }

            // 如果运单已经进入派送阶段或已签收，分配派送快递员
            if (waybillStatus.equals("派送中") || waybillStatus.equals("已签收") || waybillStatus.equals("退回")) {
                deliveryCourierId = 1 + random.nextInt(totalCouriers); // 快递员ID范围1-totalCouriers
                // 确保派送快递员与揽收快递员不同（模拟不同环节的快递员）
                while (deliveryCourierId.equals(pickupCourierId)) {
                    deliveryCourierId = 1 + random.nextInt(totalCouriers);
                }
            }

            // 生成发货和收货地区ID
            int senderRegionId = 1 + random.nextInt(totalRegions);
            int receiverRegionId = 1 + random.nextInt(totalRegions);

            stmt.setString(1, generateWaybillNumber());
            stmt.setInt(2, 1 + random.nextInt(COMPANY_NAMES.length));
            stmt.setInt(3, 1 + random.nextInt(1000));
            stmt.setString(4, generatePersonName());
            stmt.setString(5, generatePhoneNumber());
            stmt.setInt(6, senderRegionId);
            stmt.setInt(7, receiverRegionId);
            stmt.setString(8, generateAddressByRegion(senderRegionId));
            stmt.setString(9, generateAddressByRegion(receiverRegionId));
            stmt.setInt(10, 1 + random.nextInt(1500));
            stmt.setInt(11, 1 + random.nextInt(1500));
            stmt.setString(12, generateGoodsName());
            stmt.setString(13, GOODS_TYPES[random.nextInt(GOODS_TYPES.length)]);
            stmt.setBigDecimal(14, new java.math.BigDecimal(goodsWeight).setScale(3, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(15, new java.math.BigDecimal(goodsVolume).setScale(3, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(16, new java.math.BigDecimal(goodsValue).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setString(17, serviceType);
            stmt.setString(18, PAYMENT_METHODS[random.nextInt(PAYMENT_METHODS.length)]);
            stmt.setBigDecimal(19, new java.math.BigDecimal(baseFee).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(20, new java.math.BigDecimal(weightFee).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(21, new java.math.BigDecimal(distanceFee).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(22, new java.math.BigDecimal(serviceFee).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(23, new java.math.BigDecimal(insuranceFee).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(24, new java.math.BigDecimal(totalAmount).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setString(25, waybillStatus);
            stmt.setTimestamp(26, Timestamp.valueOf(createTime));
            stmt.setTimestamp(27, pickupTime != null ? Timestamp.valueOf(pickupTime) : null);
            stmt.setTimestamp(28, deliveryTime != null ? Timestamp.valueOf(deliveryTime) : null);
            stmt.setTimestamp(29, signTime != null ? Timestamp.valueOf(signTime) : null);
            stmt.setTimestamp(30, actualDeliveryTime != null ? Timestamp.valueOf(actualDeliveryTime) : null);
            if (pickupCourierId != null) {
                stmt.setInt(31, pickupCourierId);
            } else {
                stmt.setNull(31, java.sql.Types.INTEGER);
            }
            if (deliveryCourierId != null) {
                stmt.setInt(32, deliveryCourierId);
            } else {
                stmt.setNull(32, java.sql.Types.INTEGER);
            }
            stmt.addBatch();

            if (i % 100 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 8. 生成运单轨迹数据
    private void generateWaybillTracking() throws SQLException {
        String sql = "INSERT INTO waybill_tracking (waybill_id, tracking_time, location, status_description, operator, outlet_id, driver_id, courier_id, longitude, latitude) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        // 首先获取所有运单的发货和收货地区信息
        String waybillQuery = "SELECT waybill_id, sender_region_id, receiver_region_id FROM waybills ORDER BY waybill_id";
        PreparedStatement waybillStmt = connection.prepareStatement(waybillQuery);
        ResultSet waybillRs = waybillStmt.executeQuery();

        String[] trackingStatuses = {
            "快件已收寄", "快件离开始发地", "快件到达中转站", "快件离开中转站",
            "快件到达目的地", "快件正在派送", "快件已签收"
        };

        // 运单状态对应的轨迹数量
        String[] waybillStatuses = {"已下单", "已揽收", "运输中", "派送中", "已签收", "异常", "退回"};
        int[] trackingCounts = {1, 2, 4, 6, 7, 3, 5}; // 每种状态对应的轨迹记录数量

        int totalCouriers = getTotalCourierCount(); // 获取快递员总数

        while (waybillRs.next()) {
            int waybillId = waybillRs.getInt("waybill_id");
            int senderRegionId = waybillRs.getInt("sender_region_id");
            int receiverRegionId = waybillRs.getInt("receiver_region_id");

            LocalDateTime baseTime = LocalDateTime.now().minusDays(random.nextInt(30));

            // 随机选择运单状态，决定生成多少条轨迹记录
            int statusIndex = random.nextInt(waybillStatuses.length);
            int trackingCount = trackingCounts[statusIndex];

            // 根据发货和收货地区生成轨迹路径
            String senderLocation = getRegionLocation(senderRegionId);
            String receiverLocation = getRegionLocation(receiverRegionId);

            // 特殊处理异常和退回状态
            if ("异常".equals(waybillStatuses[statusIndex])) {
                // 异常状态：生成前几个正常轨迹，然后添加异常信息
                for (int j = 0; j < trackingCount - 1; j++) {
                    // 根据轨迹状态决定是否需要司机或快递员
                    Integer driverId = null;
                    Integer courierId = null;
                    String currentLocation = getTrackingLocation(j, senderLocation, receiverLocation, trackingStatuses.length);

                    if (trackingStatuses[j].contains("离开") || trackingStatuses[j].contains("到达中转站")) {
                        // 运输环节需要司机
                        driverId = 1 + random.nextInt(800);
                    } else if (trackingStatuses[j].contains("收寄") || trackingStatuses[j].contains("派送") || trackingStatuses[j].contains("签收")) {
                        // 揽收和派送环节需要快递员
                        courierId = 1 + random.nextInt(totalCouriers);
                    }

                    stmt.setInt(1, waybillId);
                    stmt.setTimestamp(2, Timestamp.valueOf(baseTime.plusHours(j * 6 + random.nextInt(6))));
                    stmt.setString(3, currentLocation);
                    stmt.setString(4, trackingStatuses[j]);
                    stmt.setString(5, generatePersonName());
                    stmt.setInt(6, 1 + random.nextInt(1500));
                    if (driverId != null) {
                        stmt.setInt(7, driverId);
                    } else {
                        stmt.setNull(7, java.sql.Types.INTEGER);
                    }
                    if (courierId != null) {
                        stmt.setInt(8, courierId);
                    } else {
                        stmt.setNull(8, java.sql.Types.INTEGER);
                    }
                    stmt.setBigDecimal(9, new java.math.BigDecimal(73 + random.nextDouble() * 62));
                    stmt.setBigDecimal(10, new java.math.BigDecimal(18 + random.nextDouble() * 35));
                    stmt.addBatch();
                }
                // 添加异常轨迹
                String exceptionLocation = getTrackingLocation(trackingCount - 1, senderLocation, receiverLocation, trackingStatuses.length);
                stmt.setInt(1, waybillId);
                stmt.setTimestamp(2, Timestamp.valueOf(baseTime.plusHours((trackingCount - 1) * 6 + random.nextInt(6))));
                stmt.setString(3, exceptionLocation);
                stmt.setString(4, "快件发生异常，正在处理中");
                stmt.setString(5, generatePersonName());
                stmt.setInt(6, 1 + random.nextInt(1500));
                stmt.setNull(7, java.sql.Types.INTEGER); // 异常处理通常不涉及特定司机
                stmt.setInt(8, 1 + random.nextInt(totalCouriers)); // 可能有快递员处理异常
                stmt.setBigDecimal(9, new java.math.BigDecimal(73 + random.nextDouble() * 62));
                stmt.setBigDecimal(10, new java.math.BigDecimal(18 + random.nextDouble() * 35));
                stmt.addBatch();
            } else if ("退回".equals(waybillStatuses[statusIndex])) {
                // 退回状态：生成到派送阶段，然后添加退回信息
                for (int j = 0; j < trackingCount - 1; j++) {
                    // 根据轨迹状态决定是否需要司机或快递员
                    Integer driverId = null;
                    Integer courierId = null;
                    String currentLocation = getTrackingLocation(j, senderLocation, receiverLocation, trackingStatuses.length);

                    if (trackingStatuses[j].contains("离开") || trackingStatuses[j].contains("到达中转站")) {
                        // 运输环节需要司机
                        driverId = 1 + random.nextInt(800);
                    } else if (trackingStatuses[j].contains("收寄") || trackingStatuses[j].contains("派送") || trackingStatuses[j].contains("签收")) {
                        // 揽收和派送环节需要快递员
                        courierId = 1 + random.nextInt(totalCouriers);
                    }

                    stmt.setInt(1, waybillId);
                    stmt.setTimestamp(2, Timestamp.valueOf(baseTime.plusHours(j * 6 + random.nextInt(6))));
                    stmt.setString(3, currentLocation);
                    stmt.setString(4, trackingStatuses[j]);
                    stmt.setString(5, generatePersonName());
                    stmt.setInt(6, 1 + random.nextInt(1500));
                    if (driverId != null) {
                        stmt.setInt(7, driverId);
                    } else {
                        stmt.setNull(7, java.sql.Types.INTEGER);
                    }
                    if (courierId != null) {
                        stmt.setInt(8, courierId);
                    } else {
                        stmt.setNull(8, java.sql.Types.INTEGER);
                    }
                    stmt.setBigDecimal(9, new java.math.BigDecimal(73 + random.nextDouble() * 62));
                    stmt.setBigDecimal(10, new java.math.BigDecimal(18 + random.nextDouble() * 35));
                    stmt.addBatch();
                }
                // 添加退回轨迹
                stmt.setInt(1, waybillId);
                stmt.setTimestamp(2, Timestamp.valueOf(baseTime.plusHours((trackingCount - 1) * 6 + random.nextInt(6))));
                stmt.setString(3, receiverLocation);
                stmt.setString(4, "快件被退回，原因：收件人拒收");
                stmt.setString(5, generatePersonName());
                stmt.setInt(6, 1 + random.nextInt(1500));
                stmt.setNull(7, java.sql.Types.INTEGER); // 退回处理通常不涉及司机
                stmt.setInt(8, 1 + random.nextInt(totalCouriers)); // 快递员处理退回
                stmt.setBigDecimal(9, new java.math.BigDecimal(73 + random.nextDouble() * 62));
                stmt.setBigDecimal(10, new java.math.BigDecimal(18 + random.nextDouble() * 35));
                stmt.addBatch();
            } else {
                // 正常状态：按顺序生成轨迹记录
                for (int j = 0; j < trackingCount; j++) {
                    // 根据轨迹状态决定是否需要司机或快递员
                    Integer driverId = null;
                    Integer courierId = null;
                    String currentLocation = getTrackingLocation(j, senderLocation, receiverLocation, trackingStatuses.length);

                    if (trackingStatuses[j].contains("离开") || trackingStatuses[j].contains("到达中转站")) {
                        // 运输环节需要司机
                        driverId = 1 + random.nextInt(800);
                    } else if (trackingStatuses[j].contains("收寄") || trackingStatuses[j].contains("派送") || trackingStatuses[j].contains("签收")) {
                        // 揽收和派送环节需要快递员
                        courierId = 1 + random.nextInt(totalCouriers);
                    }

                    stmt.setInt(1, waybillId);
                    stmt.setTimestamp(2, Timestamp.valueOf(baseTime.plusHours(j * 6 + random.nextInt(6))));
                    stmt.setString(3, currentLocation);
                    stmt.setString(4, trackingStatuses[j]);
                    stmt.setString(5, generatePersonName());
                    stmt.setInt(6, 1 + random.nextInt(1500));
                    if (driverId != null) {
                        stmt.setInt(7, driverId);
                    } else {
                        stmt.setNull(7, java.sql.Types.INTEGER);
                    }
                    if (courierId != null) {
                        stmt.setInt(8, courierId);
                    } else {
                        stmt.setNull(8, java.sql.Types.INTEGER);
                    }
                    stmt.setBigDecimal(9, new java.math.BigDecimal(73 + random.nextDouble() * 62));
                    stmt.setBigDecimal(10, new java.math.BigDecimal(18 + random.nextDouble() * 35));
                    stmt.addBatch();
                }
            }

            if (waybillId % 100 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
        waybillRs.close();
        waybillStmt.close();
    }

    // 9. 生成价格配置数据
    private void generatePricingConfig() throws SQLException {
        String sql = "INSERT INTO pricing_config (company_id, origin_region_id, dest_region_id, service_type, base_price, additional_price, distance_km, effective_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        int totalRegions = getTotalRegionCount();
        for (int i = 1; i <= 3000; i++) {
            int originRegionId = 1 + random.nextInt(totalRegions);
            int destRegionId = 1 + random.nextInt(totalRegions);

            // 确保起始地区和目的地区不相同
            while (destRegionId == originRegionId) {
                destRegionId = 1 + random.nextInt(totalRegions);
            }

            String serviceType = SERVICE_TYPES[random.nextInt(SERVICE_TYPES.length)];
            int distance = 50 + random.nextInt(2000);

            // 根据服务类型调整价格
            double basePrice, additionalPrice;
            switch (serviceType) {
                case "当日达":
                    basePrice = 15 + random.nextInt(25); // 15-40元
                    additionalPrice = 3 + random.nextInt(7); // 3-10元
                    break;
                case "次日达":
                    basePrice = 10 + random.nextInt(20); // 10-30元
                    additionalPrice = 2 + random.nextInt(6); // 2-8元
                    break;
                case "标准快递":
                    basePrice = 8 + random.nextInt(15); // 8-23元
                    additionalPrice = 2 + random.nextInt(5); // 2-7元
                    break;
                case "经济快递":
                    basePrice = 5 + random.nextInt(10); // 5-15元
                    additionalPrice = 1 + random.nextInt(4); // 1-5元
                    break;
                default:
                    basePrice = 8 + random.nextInt(20);
                    additionalPrice = 2 + random.nextInt(10);
            }

            // 根据距离调整价格（距离越远，价格越高）
            if (distance > 1000) {
                basePrice *= 1.5;
                additionalPrice *= 1.3;
            } else if (distance > 500) {
                basePrice *= 1.2;
                additionalPrice *= 1.1;
            }

            stmt.setInt(1, 1 + random.nextInt(COMPANY_NAMES.length));
            stmt.setInt(2, originRegionId);
            stmt.setInt(3, destRegionId);
            stmt.setString(4, serviceType);
            stmt.setBigDecimal(5, new java.math.BigDecimal(basePrice).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setBigDecimal(6, new java.math.BigDecimal(additionalPrice).setScale(2, java.math.RoundingMode.HALF_UP));
            stmt.setInt(7, distance);
            stmt.setDate(8, Date.valueOf(LocalDate.of(2023, 1, 1)));
            stmt.addBatch();

            if (i % 100 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 10. 生成异常信息数据
    private void generateExceptions() throws SQLException {
        String sql = "INSERT INTO exceptions (waybill_id, exception_type, exception_description, occurrence_time, location, responsible_party, compensation_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        String[] exceptionTypes = {"丢失", "损坏", "延误", "拒收", "地址错误", "联系不上", "其他"};
        String[] responsibleParties = {"快递公司", "客户", "第三方", "不可抗力"};
        String[] statuses = {"待处理", "处理中", "已解决", "已关闭"};

        // 为不同异常类型定义相应的描述
        String[][] exceptionDescriptions = {
            // 丢失
            {"包裹在运输途中丢失，无法找到", "快件在分拣中心丢失", "包裹在配送过程中遗失", "货物在中转站丢失"},
            // 损坏
            {"包装破损，内件受损", "运输过程中挤压变形", "包裹被雨水浸湿", "易碎品包装不当导致破损", "包裹外包装严重破损"},
            // 延误
            {"恶劣天气导致运输延误", "车辆故障造成配送延误", "分拣中心积压导致延误", "交通管制影响配送时间", "节假日期间配送延误"},
            // 拒收
            {"收件人拒绝签收", "货到付款金额有争议被拒收", "收件人不在家多次投递失败", "收件人对货物不满意拒收"},
            // 地址错误
            {"收件地址不详细无法投递", "收件人地址填写错误", "门牌号码不存在", "收件人搬迁地址失效"},
            // 联系不上
            {"收件人电话无法接通", "收件人电话号码错误", "收件人长期不接电话", "联系电话已停机"},
            // 其他
            {"包裹被海关扣留", "收件人身份证信息不符", "禁寄物品无法配送", "收件区域暂停配送服务"}
        };

        for (int i = 1; i <= 800; i++) {
            int typeIndex = random.nextInt(exceptionTypes.length);
            String exceptionType = exceptionTypes[typeIndex];
            String description = exceptionDescriptions[typeIndex][random.nextInt(exceptionDescriptions[typeIndex].length)];

            // 根据异常类型调整责任方概率
            String responsibleParty;
            switch (exceptionType) {
                case "丢失":
                case "损坏":
                case "延误":
                    // 这些主要是快递公司责任
                    responsibleParty = random.nextDouble() < 0.8 ? "快递公司" : responsibleParties[random.nextInt(responsibleParties.length)];
                    break;
                case "拒收":
                case "地址错误":
                case "联系不上":
                    // 这些主要是客户责任
                    responsibleParty = random.nextDouble() < 0.7 ? "客户" : responsibleParties[random.nextInt(responsibleParties.length)];
                    break;
                default:
                    responsibleParty = responsibleParties[random.nextInt(responsibleParties.length)];
            }

            // 根据异常类型调整赔偿金额
            int compensationAmount = 0;
            switch (exceptionType) {
                case "丢失":
                    compensationAmount = 50 + random.nextInt(500); // 50-550元
                    break;
                case "损坏":
                    compensationAmount = 20 + random.nextInt(300); // 20-320元
                    break;
                case "延误":
                    compensationAmount = random.nextInt(100); // 0-100元
                    break;
                default:
                    compensationAmount = random.nextInt(50); // 0-50元
            }

            // 根据异常严重程度调整处理状态
            String status;
            if (exceptionType.equals("丢失") || exceptionType.equals("损坏")) {
                // 严重异常处理状态分布
                double statusRand = random.nextDouble();
                if (statusRand < 0.1) status = "待处理";
                else if (statusRand < 0.2) status = "处理中";
                else if (statusRand < 0.8) status = "已解决";
                else status = "已关闭";
            } else {
                // 一般异常处理状态分布
                double statusRand = random.nextDouble();
                if (statusRand < 0.05) status = "待处理";
                else if (statusRand < 0.15) status = "处理中";
                else if (statusRand < 0.9) status = "已解决";
                else status = "已关闭";
            }

            stmt.setInt(1, 1 + random.nextInt(2000));
            stmt.setString(2, exceptionType);
            stmt.setString(3, description);
            stmt.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now().minusDays(random.nextInt(30))));
            stmt.setString(5, PROVINCES[random.nextInt(PROVINCES.length)]);
            stmt.setString(6, responsibleParty);
            stmt.setBigDecimal(7, new java.math.BigDecimal(compensationAmount));
            stmt.setString(8, status);
            stmt.addBatch();
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 辅助方法
    private String generatePhoneNumber() {
        return "1" + (3 + random.nextInt(6)) + String.format("%09d", random.nextInt(1000000000));
    }

    private String generatePersonName() {
        return FIRST_NAMES[random.nextInt(FIRST_NAMES.length)] +
               LAST_NAMES[random.nextInt(LAST_NAMES.length)] +
               LAST_NAMES[random.nextInt(LAST_NAMES.length)];
    }

    private String generateCompanyName() {
        String[] prefixes = {"北京", "上海", "深圳", "广州", "杭州", "南京", "成都", "武汉"};
        String[] suffixes = {"科技有限公司", "贸易有限公司", "实业有限公司", "电子商务有限公司"};
        return prefixes[random.nextInt(prefixes.length)] +
               FIRST_NAMES[random.nextInt(FIRST_NAMES.length)] +
               suffixes[random.nextInt(suffixes.length)];
    }

    private String generateEmail() {
        return "user" + random.nextInt(10000) + "@example.com";
    }

    private String generateIdCard() {
        return String.format("%06d%04d%02d%02d%04d",
            110000 + random.nextInt(900000),
            1970 + random.nextInt(30),
            1 + random.nextInt(12),
            1 + random.nextInt(28),
            random.nextInt(10000));
    }

    private String generateAddress() {
        return PROVINCES[random.nextInt(PROVINCES.length)] + "某某街道" + (1 + random.nextInt(999)) + "号";
    }

    // 根据地区ID生成对应的真实地址
    private String generateAddressByRegion(int regionId) throws SQLException {
        String sql = "SELECT region_name, province, city FROM regions WHERE region_id = ?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(1, regionId);
        ResultSet rs = stmt.executeQuery();

        String address = "未知地址";
        if (rs.next()) {
            String province = rs.getString("province");
            String city = rs.getString("city");
            String regionName = rs.getString("region_name");

            if (city != null && !city.isEmpty()) {
                // 市级地区
                address = province + city + generateStreetAddress();
            } else {
                // 省级地区
                address = province + regionName + generateStreetAddress();
            }
        }

        rs.close();
        stmt.close();
        return address;
    }

    // 生成街道地址部分
    private String generateStreetAddress() {
        String[] streetTypes = {"街道", "路", "大道", "巷", "胡同", "广场"};
        String[] streetNames = {"中山", "人民", "解放", "建设", "和平", "友谊", "光明", "新华", "文化", "商业"};

        return streetNames[random.nextInt(streetNames.length)] +
               streetTypes[random.nextInt(streetTypes.length)] +
               (1 + random.nextInt(999)) + "号";
    }

    private String generateOutletName() {
        return PROVINCES[random.nextInt(PROVINCES.length)].substring(0, 2) + "网点" + (1 + random.nextInt(999));
    }

    // 根据地区ID生成对应的真实网点名称
    private String generateOutletNameByRegion(int regionId) throws SQLException {
        String sql = "SELECT region_name, province, city FROM regions WHERE region_id = ?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(1, regionId);
        ResultSet rs = stmt.executeQuery();

        String outletName = "未知网点";
        if (rs.next()) {
            String province = rs.getString("province");
            String city = rs.getString("city");
            String regionName = rs.getString("region_name");

            String[] outletTypes = {"分拣中心", "配送站", "营业部", "服务点", "转运中心"};
            String outletType = outletTypes[random.nextInt(outletTypes.length)];

            if (city != null && !city.isEmpty()) {
                // 市级地区：如"北京朝阳分拣中心"
                outletName = province + city + outletType;
            } else {
                // 省级地区：如"北京分拣中心"
                outletName = province + outletType;
            }
        }

        rs.close();
        stmt.close();
        return outletName;
    }

    private String generateLicensePlate() {
        String[] provinces = {"京", "沪", "粤", "苏", "浙", "鲁", "豫", "川"};
        return provinces[random.nextInt(provinces.length)] +
               (char)('A' + random.nextInt(26)) +
               String.format("%05d", random.nextInt(100000));
    }

    private String generateLicenseNumber() {
        return String.format("%06d%08d",
            110000 + random.nextInt(900000),
            random.nextInt(100000000));
    }

    private String generateWaybillNumber() {
        return COMPANY_CODES[random.nextInt(COMPANY_CODES.length)] +
               String.format("%013d", System.currentTimeMillis() / 1000) +
               String.format("%06d", waybillSequence++);
    }

    private String generateGoodsName() {
        String[] goods = {"手机", "电脑", "衣服", "鞋子", "书籍", "食品", "化妆品", "玩具", "家电", "文具"};
        return goods[random.nextInt(goods.length)];
    }

    // 生成服务区域
    private String generateServiceArea() {
        String[] areas = {
            "朝阳区", "海淀区", "西城区", "东城区", "丰台区", "石景山区",
            "浦东新区", "黄浦区", "静安区", "徐汇区", "长宁区", "普陀区",
            "福田区", "南山区", "罗湖区", "盐田区", "宝安区", "龙岗区",
            "天河区", "越秀区", "荔湾区", "海珠区", "白云区", "番禺区"
        };
        return areas[random.nextInt(areas.length)];
    }

    // 获取网点总数
    private int getTotalOutletCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM outlets";
        PreparedStatement stmt = connection.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        stmt.close();
        return count > 0 ? count : 500; // 如果查询失败，返回默认值500
    }

    // 获取快递员总数
    private int getTotalCourierCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM couriers";
        PreparedStatement stmt = connection.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        stmt.close();
        return count > 0 ? count : 500; // 如果查询失败，返回默认值500
    }

    // 11. 生成客户满意度数据
    private void generateCustomerSatisfaction() throws SQLException {
        String sql = "INSERT INTO customer_satisfaction (waybill_id, customer_id, company_id, overall_score, speed_score, service_score, packaging_score, price_score, feedback_content, feedback_type, response_time, is_resolved, survey_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        // 按反馈类型分类的反馈内容
        String[] praiseFeedbacks = {
            "服务很好，快递员态度很棒", "包装完好，送货及时", "价格合理，服务满意",
            "非常满意的服务体验", "快递员很专业，服务态度好", "包装很仔细，没有损坏",
            "送货速度很快，很满意", "客服回复及时，解决问题很快", "整体服务质量很高"
        };

        String[] suggestionFeedbacks = {
            "快递速度有待提高", "包装需要改进", "希望能提供更多配送时间选择",
            "建议增加短信通知功能", "希望快递员能提前电话联系", "包装可以更环保一些",
            "建议优化配送路线", "希望能提供更准确的配送时间", "建议改进客服响应速度"
        };

        String[] complaintFeedbacks = {
            "快递延误严重，影响使用", "包装破损，货物受损", "快递员态度恶劣，服务差",
            "多次投递失败，没有通知", "货物丢失，处理不及时", "收费不合理，乱收费",
            "客服态度差，问题解决不了", "配送时间不准确，经常延误", "包装简陋，货物损坏严重"
        };

        String[] feedbackTypes = {"表扬", "建议", "投诉"};

        for (int i = 1; i <= 2500; i++) {
            // 根据反馈类型确定评分范围
            String feedbackType = feedbackTypes[random.nextInt(feedbackTypes.length)];
            int overallScore;
            String feedbackContent;

            switch (feedbackType) {
                case "表扬":
                    overallScore = 4 + random.nextInt(2); // 4-5分
                    feedbackContent = praiseFeedbacks[random.nextInt(praiseFeedbacks.length)];
                    break;
                case "建议":
                    overallScore = 3 + random.nextInt(2); // 3-4分
                    feedbackContent = suggestionFeedbacks[random.nextInt(suggestionFeedbacks.length)];
                    break;
                case "投诉":
                    overallScore = 1 + random.nextInt(2); // 1-2分
                    feedbackContent = complaintFeedbacks[random.nextInt(complaintFeedbacks.length)];
                    break;
                default:
                    overallScore = 3;
                    feedbackContent = "一般";
            }

            stmt.setInt(1, 1 + random.nextInt(2000)); // 运单ID
            stmt.setInt(2, 1 + random.nextInt(1000)); // 客户ID
            stmt.setInt(3, 1 + random.nextInt(COMPANY_NAMES.length)); // 公司ID
            stmt.setInt(4, overallScore); // 总体评分
            stmt.setInt(5, Math.max(1, Math.min(5, overallScore + random.nextInt(3) - 1))); // 时效评分
            stmt.setInt(6, Math.max(1, Math.min(5, overallScore + random.nextInt(3) - 1))); // 服务评分
            stmt.setInt(7, Math.max(1, Math.min(5, overallScore + random.nextInt(3) - 1))); // 包装评分
            stmt.setInt(8, Math.max(1, Math.min(5, overallScore + random.nextInt(3) - 1))); // 价格评分
            stmt.setString(9, feedbackContent); // 反馈内容
            stmt.setString(10, feedbackType); // 反馈类型
            stmt.setInt(11, feedbackType.equals("投诉") ? 1 + random.nextInt(48) : 1 + random.nextInt(24)); // 响应时间（投诉处理时间更长）
            stmt.setBoolean(12, feedbackType.equals("投诉") ? random.nextInt(10) < 7 : random.nextBoolean()); // 投诉70%已解决，其他随机
            stmt.setDate(13, Date.valueOf(LocalDate.now().minusDays(random.nextInt(90)))); // 调研日期
            stmt.addBatch();

            if (i % 100 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 12. 生成运输路线数据
    private void generateTransportRoutes() throws SQLException {
        String sql = "INSERT INTO transport_routes (route_code, route_name, company_id, origin_outlet_id, dest_outlet_id, route_type, transport_mode, distance_km, estimated_time, route_cost, fuel_consumption, toll_fee, frequency_per_day, max_load_weight, max_load_volume, route_status, weather_impact, traffic_condition) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        String[] transportModes = {"公路", "铁路", "航空", "水运", "多式联运"};
        String[] routeTypes = {"干线运输", "支线运输", "同城配送", "最后一公里"};
        String[] routeStatuses = {"正常", "暂停", "维护"};
        String[] weatherImpacts = {"无影响", "轻微影响", "严重影响"};
        String[] trafficConditions = {"畅通", "缓慢", "拥堵"};

        for (int i = 1; i <= 800; i++) {
            int originOutletId = 1 + random.nextInt(1500); // 网点ID范围
            int destOutletId = 1 + random.nextInt(1500);
            while (destOutletId == originOutletId) {
                destOutletId = 1 + random.nextInt(1500);
            }

            // 获取起始和目的网点的地区信息来生成路线名称
            String routeName = generateRouteNameByOutlets(originOutletId, destOutletId);

            int distance = 100 + random.nextInt(2000);
            String transportMode = transportModes[random.nextInt(transportModes.length)];
            double estimatedTime = distance / (transportMode.equals("航空") ? 800.0 : 60.0) + 1; // 预计时间（小时）

            // 根据概率分配状态
            String routeStatus;
            int statusRand = random.nextInt(100);
            if (statusRand < 80) {
                routeStatus = "正常"; // 80%正常
            } else if (statusRand < 95) {
                routeStatus = "暂停"; // 15%暂停
            } else {
                routeStatus = "维护"; // 5%维护
            }

            // 根据概率分配天气影响
            String weatherImpact;
            int weatherRand = random.nextInt(100);
            if (weatherRand < 70) {
                weatherImpact = "无影响"; // 70%无影响
            } else if (weatherRand < 90) {
                weatherImpact = "轻微影响"; // 20%轻微影响
            } else {
                weatherImpact = "严重影响"; // 10%严重影响
            }

            // 根据概率分配交通状况
            String trafficCondition;
            int trafficRand = random.nextInt(100);
            if (trafficRand < 60) {
                trafficCondition = "畅通"; // 60%畅通
            } else if (trafficRand < 85) {
                trafficCondition = "缓慢"; // 25%缓慢
            } else {
                trafficCondition = "拥堵"; // 15%拥堵
            }

            stmt.setString(1, String.format("RT%06d", i)); // route_code
            stmt.setString(2, routeName); // route_name
            stmt.setInt(3, 1 + random.nextInt(COMPANY_NAMES.length)); // company_id
            stmt.setInt(4, originOutletId); // origin_outlet_id
            stmt.setInt(5, destOutletId); // dest_outlet_id
            stmt.setString(6, routeTypes[random.nextInt(routeTypes.length)]); // route_type
            stmt.setString(7, transportMode); // transport_mode
            stmt.setBigDecimal(8, new java.math.BigDecimal(distance)); // distance_km
            stmt.setBigDecimal(9, new java.math.BigDecimal(estimatedTime).setScale(2, java.math.RoundingMode.HALF_UP)); // estimated_time
            stmt.setBigDecimal(10, new java.math.BigDecimal(distance * 2.0 + random.nextInt(500))); // route_cost
            stmt.setBigDecimal(11, new java.math.BigDecimal(8.0 + random.nextDouble() * 4.0).setScale(2, java.math.RoundingMode.HALF_UP)); // fuel_consumption
            stmt.setBigDecimal(12, new java.math.BigDecimal(distance * 0.5 + random.nextInt(100))); // toll_fee
            stmt.setInt(13, 1 + random.nextInt(10)); // frequency_per_day
            stmt.setBigDecimal(14, new java.math.BigDecimal(5.0 + random.nextDouble() * 15.0).setScale(2, java.math.RoundingMode.HALF_UP)); // max_load_weight
            stmt.setBigDecimal(15, new java.math.BigDecimal(20.0 + random.nextDouble() * 30.0).setScale(2, java.math.RoundingMode.HALF_UP)); // max_load_volume
            stmt.setString(16, routeStatus); // route_status
            stmt.setString(17, weatherImpact); // weather_impact
            stmt.setString(18, trafficCondition); // traffic_condition
            stmt.addBatch();

            if (i % 50 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 13. 生成客户行为分析数据
    private void generateCustomerBehavior() throws SQLException {
        String sql = "INSERT INTO customer_behavior (customer_id, behavior_date, behavior_type, behavior_detail, channel, device_type, session_duration, page_views, conversion_flag, order_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        String[] behaviorTypes = {"下单", "查询", "投诉", "评价", "退货", "续费"};
        String[] channels = {"官网", "手机APP", "微信", "电话", "线下网点"};
        String[] deviceTypes = {"PC", "手机", "平板", "其他"};

        // 为不同行为类型定义相应的行为详情
        String[][] behaviorDetails = {
            // 下单
            {"客户通过手机APP下单", "客户在官网提交订单", "客户电话下单", "客户在线下网点下单", "客户通过微信小程序下单"},
            // 查询
            {"客户查询物流信息", "客户查询运费价格", "客户查询配送时间", "客户查询网点信息", "客户查询服务范围"},
            // 投诉
            {"客户投诉服务质量", "客户投诉配送延误", "客户投诉包裹损坏", "客户投诉态度问题", "客户投诉收费争议"},
            // 评价
            {"客户对服务进行评价", "客户对配送员进行评价", "客户对包装质量评价", "客户对时效性评价", "客户对整体体验评价"},
            // 退货
            {"客户申请退货", "客户要求退款", "客户申请换货", "客户取消订单", "客户拒收包裹"},
            // 续费
            {"客户续费会员服务", "客户升级VIP服务", "客户购买增值服务", "客户续费保险服务", "客户开通月卡服务"}
        };

        for (int customerId = 1; customerId <= 3000; customerId++) {
            // 每个客户生成1-5条行为记录
            int behaviorCount = 1 + random.nextInt(5);
            for (int i = 0; i < behaviorCount; i++) {
                LocalDate behaviorDate = LocalDate.now().minusDays(random.nextInt(90));
                int typeIndex = random.nextInt(behaviorTypes.length);
                String behaviorType = behaviorTypes[typeIndex];
                String behaviorDetail = behaviorDetails[typeIndex][random.nextInt(behaviorDetails[typeIndex].length)];

                // 根据行为类型调整订单金额
                double orderAmount = 0.0;
                boolean conversionFlag = false;
                if ("下单".equals(behaviorType)) {
                    orderAmount = 20 + random.nextDouble() * 80;
                    conversionFlag = true;
                } else if ("续费".equals(behaviorType)) {
                    orderAmount = 50 + random.nextDouble() * 200; // 续费金额通常较高
                    conversionFlag = true;
                } else {
                    // 其他行为类型可能产生转化
                    conversionFlag = random.nextDouble() < 0.1; // 10%转化率
                    if (conversionFlag) {
                        orderAmount = 20 + random.nextDouble() * 80;
                    }
                }

                // 根据行为类型调整会话时长和页面浏览量
                int sessionDuration, pageViews;
                switch (behaviorType) {
                    case "下单":
                        sessionDuration = 300 + random.nextInt(900); // 5-20分钟
                        pageViews = 5 + random.nextInt(15); // 5-20页
                        break;
                    case "查询":
                        sessionDuration = 60 + random.nextInt(300); // 1-6分钟
                        pageViews = 2 + random.nextInt(8); // 2-10页
                        break;
                    case "投诉":
                        sessionDuration = 600 + random.nextInt(1200); // 10-30分钟
                        pageViews = 3 + random.nextInt(7); // 3-10页
                        break;
                    case "评价":
                        sessionDuration = 120 + random.nextInt(300); // 2-7分钟
                        pageViews = 2 + random.nextInt(5); // 2-7页
                        break;
                    case "退货":
                        sessionDuration = 300 + random.nextInt(600); // 5-15分钟
                        pageViews = 3 + random.nextInt(8); // 3-11页
                        break;
                    case "续费":
                        sessionDuration = 180 + random.nextInt(420); // 3-10分钟
                        pageViews = 3 + random.nextInt(7); // 3-10页
                        break;
                    default:
                        sessionDuration = 30 + random.nextInt(600);
                        pageViews = 1 + random.nextInt(20);
                }

                stmt.setInt(1, customerId); // customer_id
                stmt.setDate(2, Date.valueOf(behaviorDate)); // behavior_date
                stmt.setString(3, behaviorType); // behavior_type
                stmt.setString(4, behaviorDetail); // behavior_detail
                stmt.setString(5, channels[random.nextInt(channels.length)]); // channel
                stmt.setString(6, deviceTypes[random.nextInt(deviceTypes.length)]); // device_type
                stmt.setInt(7, sessionDuration); // session_duration (秒)
                stmt.setInt(8, pageViews); // page_views
                stmt.setBoolean(9, conversionFlag); // conversion_flag
                stmt.setBigDecimal(10, new java.math.BigDecimal(orderAmount).setScale(2, java.math.RoundingMode.HALF_UP)); // order_amount
                stmt.addBatch();

                if ((customerId * behaviorCount + i) % 100 == 0) {
                    stmt.executeBatch();
                }
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

    // 14. 生成时效分析数据
    private void generateDeliveryTimeAnalysis() throws SQLException {
        String sql = "INSERT INTO delivery_time_analysis (waybill_id, company_id, service_type, origin_region_id, dest_region_id, distance_km, promised_delivery_time, actual_delivery_time, time_difference, is_on_time, delay_reason, pickup_time_cost, sorting_time_cost, transport_time_cost, delivery_time_cost, weekend_flag, holiday_flag, weather_condition) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = connection.prepareStatement(sql);

        String[] delayReasons = {"交通拥堵", "天气原因", "设备故障", "人员不足", "地址错误", "客户原因", "其他"};
        String[] weatherConditions = {"晴天", "雨天", "雪天", "雾天", "恶劣天气"};

        int totalRegions = getTotalRegionCount();
        for (int waybillId = 1; waybillId <= 8000; waybillId++) {
            int companyId = 1 + random.nextInt(COMPANY_NAMES.length);
            String serviceType = SERVICE_TYPES[random.nextInt(SERVICE_TYPES.length)];
            int originRegionId = 1 + random.nextInt(totalRegions);
            int destRegionId = 1 + random.nextInt(totalRegions);
            while (destRegionId == originRegionId) {
                destRegionId = 1 + random.nextInt(totalRegions);
            }

            int distance = 100 + random.nextInt(2000);

            // 根据服务类型设置承诺时效
            int promisedTime;
            switch (serviceType) {
                case "当日达":
                    promisedTime = 8 + random.nextInt(4); // 8-12小时
                    break;
                case "次日达":
                    promisedTime = 20 + random.nextInt(8); // 20-28小时
                    break;
                case "标准快递":
                    promisedTime = 48 + random.nextInt(24); // 48-72小时
                    break;
                case "经济快递":
                    promisedTime = 72 + random.nextInt(48); // 72-120小时
                    break;
                default:
                    promisedTime = 48 + random.nextInt(24);
            }

            // 根据距离调整承诺时效
            if (distance > 1500) {
                promisedTime = (int)(promisedTime * 1.5);
            } else if (distance > 800) {
                promisedTime = (int)(promisedTime * 1.2);
            }

            boolean isWeekend = random.nextBoolean();
            boolean isHoliday = random.nextInt(10) == 0; // 10%概率是节假日
            String weatherCondition = weatherConditions[random.nextInt(weatherConditions.length)];

            // 根据外部因素调整实际时效
            double timeFactor = 1.0;
            if (isWeekend) timeFactor *= 1.1; // 周末延长10%
            if (isHoliday) timeFactor *= 1.3; // 节假日延长30%
            if ("恶劣天气".equals(weatherCondition)) timeFactor *= 1.4; // 恶劣天气延长40%
            else if ("雨天".equals(weatherCondition) || "雪天".equals(weatherCondition)) timeFactor *= 1.15; // 雨雪天气延长15%

            // 生成实际时效（80%-130%的波动范围）
            int actualTime = (int)(promisedTime * timeFactor * (0.8 + random.nextDouble() * 0.5));
            int timeDifference = actualTime - promisedTime;
            boolean isOnTime = timeDifference <= 0;

            // 合理分配各环节耗时
            int pickupTime, sortingTime, transportTime, deliveryTime;

            // 揽收时间：1-6小时，根据服务类型调整
            if ("当日达".equals(serviceType)) {
                pickupTime = 1 + random.nextInt(2); // 当日达揽收更快
            } else {
                pickupTime = 1 + random.nextInt(5);
            }

            // 分拣时间：2-12小时，根据距离和服务类型调整
            if (distance > 1000) {
                sortingTime = 4 + random.nextInt(8); // 长距离分拣时间更长
            } else {
                sortingTime = 2 + random.nextInt(6);
            }

            // 配送时间：1-8小时
            deliveryTime = 1 + random.nextInt(7);

            // 运输时间：总时间减去其他环节时间
            transportTime = Math.max(1, actualTime - pickupTime - sortingTime - deliveryTime);

            // 如果运输时间过短，重新调整各环节时间
            if (transportTime < actualTime * 0.4) { // 运输时间应占总时间的40%以上
                transportTime = (int)(actualTime * (0.4 + random.nextDouble() * 0.4)); // 40%-80%
                int remainingTime = actualTime - transportTime;
                pickupTime = Math.max(1, remainingTime * 20 / 100); // 20%
                sortingTime = Math.max(1, remainingTime * 50 / 100); // 50%
                deliveryTime = Math.max(1, remainingTime - pickupTime - sortingTime); // 剩余时间
            }

            stmt.setInt(1, waybillId); // waybill_id
            stmt.setInt(2, companyId); // company_id
            stmt.setString(3, serviceType); // service_type
            stmt.setInt(4, originRegionId); // origin_region_id
            stmt.setInt(5, destRegionId); // dest_region_id
            stmt.setBigDecimal(6, new java.math.BigDecimal(distance)); // distance_km
            stmt.setInt(7, promisedTime); // promised_delivery_time
            stmt.setInt(8, actualTime); // actual_delivery_time
            stmt.setInt(9, timeDifference); // time_difference
            stmt.setBoolean(10, isOnTime); // is_on_time
            stmt.setString(11, !isOnTime ? delayReasons[random.nextInt(delayReasons.length)] : null); // delay_reason
            stmt.setInt(12, pickupTime); // pickup_time_cost
            stmt.setInt(13, sortingTime); // sorting_time_cost
            stmt.setInt(14, transportTime); // transport_time_cost
            stmt.setInt(15, deliveryTime); // delivery_time_cost
            stmt.setBoolean(16, isWeekend); // weekend_flag
            stmt.setBoolean(17, isHoliday); // holiday_flag
            stmt.setString(18, weatherCondition); // weather_condition
            stmt.addBatch();

            if (waybillId % 100 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        stmt.close();
    }

}
